namespace :mysql_import do 
  #按顺序执行
  #    mysql -u root -p 
  #>>  create database old_news;
  #>>  use old_news;
  #>>  source /db/test.sql;
  # rake db:create
  # rake db:migrate
  # rake mysql_import:data 
  # rake mysql_import:new
  # rake mysql_import:notify
  # rake db:seed
  # rake news_groups:old_news
  # rake category:update_categories
  # rake reports_notifies:update

  desc "mysql数据导入postgresql数据库"
  task :data => :environment do 
    client = client_connection

    client.query("select *from department").each do |row|      
      department = Department.create(
        :id => row['id'],
        :name => row['deptname'],
        :short_name => row['jc']
      )
      unless department.valid?
        puts "=====department....=id: #{row['id']}==="
        puts "errors:#{department.errors.full_messages}"
      end
    end
    Department.find_by_sql("
      select setval('departments_id_seq', max(id)) from departments")

    client.query("select *from old_news.group").each do |row|      
      group = Group.create(
        :id => row['id'],
        :name => row['groupname']
      )
      unless group.valid?
        puts "=====group....=id: #{row['id']}==="
        puts "errors:#{group.errors.full_messages}"
      end
    end
    Group.find_by_sql("
      select setval('groups_id_seq', max(id)) from groups")

    client.query("select *from category").each do |row|      
      category = Category.create(
        :id => row['id'],
        :enmae => row['enmae'],
        :cataname => row['cataname'],
        :father => row['father']
      )
      unless category.valid?
        puts "=====category....=id: #{row['id']}==="
        puts "errors:#{category.errors.full_messages}"
      end
    end
    Category.find_by_sql("
      select setval('categories_id_seq', max(id)) from categories")

    client.query("select *from sptitle").each do |row|      
      sptitle = Sptitle.create(
        :id => row['id'],
        :name => row["name"],
        :over => row['over'],
        :orders => row["orders"]
      )

      unless sptitle.valid?
        puts "=====sptitle....=id: #{row['id']}==="
        puts "errors:#{sptitle.errors.full_messages}"
      end
    end
    Sptitle.find_by_sql("
      select setval('sptitles_id_seq', max(id)) from sptitles")

    client.query("select *from toback").each do |row|
      # puts "=====toback....=id: #{row['id']}==="
      Toback.create(
        :id => row["id"],
        :reason => row['reason']
      )
    end
    Toback.find_by_sql("
      select setval('toback_id_seq', max(id)) from toback")

    client.query("select *from user").each do |row|      
      depart = Department.where(:name => row["deptname"]).first
      email = row["email"] 
      email = "#{row['user']}@qq.com" if email.blank?
      user = User.create(
        :id => row["id"],
        :login => row['user'],
        :email => email,
        :name => row['name'],
        :office_phone => row['office_phone'],
        :mobile => row["mobile"],
        :password => row["pwd"],
        :department_id => depart.try(:id)
      )     

      if user.valid?
        GroupUser.create(:group_id => row["group"], :user_id => user.id)
      else
        puts "=====user....=id: #{row['id']}==="
        puts "errors:#{user.errors.full_messages}"
      end
    end
    User.find_by_sql("
      select setval('users_id_seq', max(id)) from users")

    client.query("select *from report").each do |row|
       info = {
        :id => row['id'],
        :title => row['title'],
        :place => row['place'],
        :reporter => row['reporter'],
        :time => row['time'],
        :screen => row['screen'],
        :check => row['check'],
        :content => row["content"],
        :note => row['note'],
        :writer => row['writer'],
        :postby => row['postby'],
        :fromwhere => row['fromwhere'],
        :department => row['department']
      }     
      
      report = Report.create(info)
      unless report.valid?
        puts "=====report....=id: #{row['id']}==="
        puts "errors:#{report.errors.full_messages}"
      end
    end
    Report.find_by_sql("
      select setval('reports_id_seq', max(id)) from reports")

  end

  desc "通知导入"
  task :notify => :environment do 
    client = client_connection

    client.query("select * from data").each do |row|      
      notify = Notify.create(
        :id => row['id'],
        :title => row['title'],
        :content => row['content'],
        :hits => row['hits'],
        :fromwhere => row['fromwhere'],
        :postby => row['postby'],
        :writer => row['writer'],
        :created_at => get_datetime(row['addtime']),
        :deleted => get_datetime(row['gqsj'])
      )
      unless notify.valid?
        puts "=====notify....=id: #{row['id']}==="
        puts "errors:#{notify.errors.full_messages}"
      end
    end
  end


  #报错 2006-05-00 14:16:01

  desc "新闻导入"
  task :new => :environment do
    client = client_connection

    client.query("update news set pubtime='2006-05-01 14:16:01' where  pubtime='2006-05-00 14:16:01'")

    create_row = lambda do |row|
      puts "==new=#{row['id']}========="
      category = nil
      category = Category.where(:id => row['cataid']).first if row['cataid'].present?

      NewsSptitle.create(:news_id => row['id'], :sptitle_id => row['sptitle']) if row['sptitle'].present?

      department = nil
      department = Department.where(:name => row['fromwhere']).first if row['fromwhere'].present?

      toback = nil
      if row["toback"].present?
        toback = Toback.where(:id => row["toback"].split("|").select(&:present?)[0])
      end

      edittime = row['edittime']
      if edittime.present?
        edittime = begin
          edittime.to_datetime
        rescue Exception => e
          nil
        end
      end

      pubtime = row['pubtime']
      if pubtime.present?
        pubtime = begin
          pubtime.to_datetime
        rescue Exception => e
          nil
        end
      end

      News.create(
        :id => row['id'],
        :category_id => category.try(:id),
        :is_important_news => row["sftj"],
        :title => row["title"],
        :bottomtitle => row["bottomtitle"],
        :uptitle => row["uptitle"],
        :author_name => row["writer"],
        :cameraman_name => row["photowriter"],
        :phone => row["phone"],
        :contribution_units => row['fromwhere'],
        :contribution_units_id => department.try(:id),
        :contribution_units_member => row["fromwhere2"],
        :writer => row["writer"],        
        :postby => row["postby"],
        :body => row['content'],
        :keywords => row['keywords'],
        :toback_reason => toback.try(:reason),
        :toback_count => row["backcount"],
        :check => row["check"],
        :edit => row["edit"],
        :hits => row["hits"],
        :pubwhere => row['pubwhere'],
        :puburl => row['puburl'],
        :pubversion => row['pubversion'],
        :pubtime => pubtime,
        :edittime => edittime ,
        :editby => row['editby'],
        :english => row['english']
      )
    end

    puts "========================1====================="
    client.query('select * from news').each do |row|
      create_row.call(row)
    end

    # puts "========================2====================="
    # client.query('select *from news limit 5000 offset 5000').each do |row|
    #   create_row.call(row)
    # end

    # puts "========================3====================="
    # client.query('select *from news limit 5000 offset 10000').each do |row|
    #   create_row.call(row)
    # end

    # puts "========================4====================="
    # client.query('select *from news limit 5000 offset 15000').each do |row|
    #   create_row.call(row)
    # end 

    # puts "========================5====================="
    # client.query('select *from news limit 5000 offset 20000').each do |row|
    #   create_row.call(row)
    # end

    # puts "========================6====================="
    # client.query('select *from news limit 5000 offset 25000').each do |row|
    #   create_row.call(row)
    # end

    # puts "========================7====================="
    # client.query('select *from news limit 5000 offset 30000').each do |row|
    #   create_row.call(row)
    # end

    # puts "========================8====================="
    # client.query('select *from news limit 5000 offset 35000').each do |row|
    #   create_row.call(row)
    # end

    # puts "========================9====================="
    # client.query('select *from news limit 5000 offset 40000').each do |row|
    #   create_row.call(row)
    # end

    News.find_by_sql("
      select setval('news_id_seq', max(id)) from news")
  end

  #desc "更新栏目信息，并更新新闻，为了新闻栏目入口"
  #task :create_and_update_categories => :environment do
  #  # 新栏目
  #  zsjy_category = Category.create(cataname: "招生就业", father:0)
  #  zs_category = Category.create(cataname: "招生", father: zsjy_category.id)
  #
  #  # 新栏目
  #  Category.create(cataname: "学术交流", father:38)
  #
  #  # 新栏目
  #  tshl_category = Category.create(cataname: "图说华理", father:0)
  #
  #  # 将新闻的栏目更新为新栏目
  #  NewsSptitle.where(sptitle_id: 68).pluck(:id).each do |news_id|
  #    news = News.where(id: news_id).first
  #    news.update_attributes(category_id: tshl_category.id) if news
  #  end
  #end

  desc "更新内容信息"
  task :update_home_location_and_check => :environment do
    New
  end


  desc "清除数据"
  task :clear => :environment do 
    puts "start......."
    Department.delete_all
    Group.delete_all
    Category.delete_all
    Sptitle.delete_all
    Toback.delete_all
    User.delete_all
    GroupUser.delete_all
    Report.delete_all
    Notify.delete_all
    News.delete_all
    puts "end......."
  end

  private
  def client_connection
    Mysql2::Client.new(
      :host => "localhost", 
      :username => "root",
      :password => "xb_2014",
      :database => "old_news")
  end

  def get_datetime str
    str = str.to_datetime if str
    return str 
  end
   

end